//
//  DatabaseManager.m
//  iWeddingDirectory
//
//  Created by Tung Ngo on 6/1/12.
//  Copyright (c) 2012 Home. All rights reserved.
//

#import "DatabaseManager.h"
#import "sqlite3.h"

@implementation DatabaseManager

+ (DatabaseManager *)sharedInstance
{
    static DatabaseManager *sharedInstance = nil;
    if (sharedInstance == nil) {
        sharedInstance = [[DatabaseManager alloc] init];
    }
    return sharedInstance;
}

- (void)createTablePosts
{
    sqlite3 *database = nil;
    NSString *pathToDatabase = [[NSHomeDirectory() stringByAppendingPathComponent:@"Documents"] stringByAppendingPathComponent:@"iwed.sqlite3"];
    int result = sqlite3_open([pathToDatabase UTF8String], &database);
    if (result == SQLITE_OK) {
        char *errorMessage;
        const char *sql = "CREATE TABLE  IF NOT EXISTS Posts (postId INTEGER, title TEXT, content TEXT, thumbnail TEXT)";
        result = sqlite3_exec(database, sql, NULL, NULL, &errorMessage);
        if (result != SQLITE_OK) {
            printf("error: %s\n", errorMessage);
        }
    }
    sqlite3_close(database);
}

- (void)addPost:(Post *)post
{
    sqlite3 *database = nil;
    NSString *pathToDatabase = [[NSHomeDirectory() stringByAppendingPathComponent:@"Documents"] stringByAppendingPathComponent:@"iwed.sqlite3"];
    int result = sqlite3_open([pathToDatabase UTF8String], &database);
    if (result == SQLITE_OK) {
        char *errorMessage;
        NSString *sql = [NSString stringWithFormat:@"INSERT INTO Posts (postId,title,content,thumbnail) VALUES ('%d','%@','%@','%@')", post.postId, post.title, post.content, post.thumbnail];
        result = sqlite3_exec(database, [sql UTF8String], NULL, NULL, &errorMessage);
        if (result != SQLITE_OK) {
            printf("error: %s\n", errorMessage);
        }
    }
    sqlite3_close(database);
}

- (void)removePost:(Post *)post
{
    sqlite3 *database = nil;
    NSString *pathToDatabase = [[NSHomeDirectory() stringByAppendingPathComponent:@"Documents"] stringByAppendingPathComponent:@"iwed.sqlite3"];
    int result = sqlite3_open([pathToDatabase UTF8String], &database);
    if (result == SQLITE_OK) {
        char *errorMessage;
        NSString *sql = [NSString stringWithFormat:@"DELETE FROM Posts WHERE postId = '%d'", post.postId];
        result = sqlite3_exec(database, [sql UTF8String], NULL, NULL, &errorMessage);
        if (result != SQLITE_OK) {
            printf("error: %s\n", errorMessage);
        }
    }
    sqlite3_close(database);
    printf("remove post id = %d", post.postId);
}

- (NSArray *)getPosts
{
    NSMutableArray *posts = [[[NSMutableArray alloc] initWithCapacity:0] autorelease];
    sqlite3 *database = nil;
    NSString *pathToDatabase = [[NSHomeDirectory() stringByAppendingPathComponent:@"Documents"] stringByAppendingPathComponent:@"iwed.sqlite3"];
    int result = sqlite3_open([pathToDatabase UTF8String], &database);
    if (result == SQLITE_OK) {
        char *errorMessage = nil;
        const char *sql = "SELECT * FROM Posts";
        sqlite3_stmt *statement = nil;
        result = sqlite3_prepare_v2(database, sql, -1, &statement, nil);
        if (result == SQLITE_OK) {
            while (sqlite3_step(statement) == SQLITE_ROW) {
                int postId = (int)sqlite3_column_int(statement, 0);
                char *szTitle = (char *)sqlite3_column_text(statement, 1);
                char *szContent = (char *)sqlite3_column_text(statement, 2);
                char *szThumbnail = (char *)sqlite3_column_text(statement, 3);
                
                Post *post = [[Post alloc] init];
                post.postId = postId;
                post.title = [NSString stringWithUTF8String:szTitle];
                post.content = [NSString stringWithUTF8String:szContent];
                post.thumbnail = [NSString stringWithUTF8String:szThumbnail];
                [posts addObject:post];
                [post release];
            }
        }
        else {
            printf("error: %s\n", errorMessage);
        }
    }
    sqlite3_close(database);
    return posts;
}

- (BOOL)isPostExisted:(int)postId
{
    sqlite3 *database = nil;
    NSString *pathToDatabase = [[NSHomeDirectory() stringByAppendingPathComponent:@"Documents"] stringByAppendingPathComponent:@"iwed.sqlite3"];
    int result = sqlite3_open([pathToDatabase UTF8String], &database);
    BOOL ret = NO;
    if (result == SQLITE_OK) {
        char *errorMessage = nil;
        NSString *sql = [NSString stringWithFormat:@"SELECT * FROM Posts WHERE postId = '%d' LIMIT 1", postId];
        sqlite3_stmt *statement = nil;
        result = sqlite3_prepare_v2(database, [sql UTF8String], -1, &statement, nil);
        if (result == SQLITE_OK) {
            while (sqlite3_step(statement) == SQLITE_ROW) {
                //return YES;
                ret = YES;
            }
        }
        else {
            printf("error: %s\n", errorMessage);
        }
    }
    sqlite3_close(database);
    printf("not existed post id = %d", postId);
    return ret;
}

@end
